﻿
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fd_Expediente_Create]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[fd_Expediente_Create]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE fd_Expediente_Create
(
	@expediente	TEXT,
	@ID INT OUTPUT
)
AS


BEGIN

	BEGIN TRY
		SET DATEFORMAT ymd;
		DECLARE @XMLData XML
		
		SET @XMLData = CONVERT(XML, @expediente)
		
		INSERT INTO Expedientes 
			SELECT a.b.value('NoExpediente[1]','varchar(50)') AS NoExpediente,
				   a.b.value('StartDate[1]','datetime') AS StartDate,
				   a.b.value('ModifyDate[1]','datetime') AS ModifyDate,
				   a.b.value('CreatedBy[1]','varchar(50)') AS CreatedBy,
				   a.b.value('ModifiedBy[1]','varchar(50)') AS ModifiedBy,
				   a.b.value('Descripcion[1]','varchar(250)') AS Descripcion
			FROM @XMLData.nodes('Expediente') a(b);
			
		SELECT ID = SCOPE_IDENTITY()
		
	END TRY
   	
   	BEGIN CATCH
   		DECLARE 	@ErrorNumber	INT,
					@ErrorSeverity	INT,
					@ErrorState		INT,
					@ErrorProcedure NVARCHAR(126),
					@ErrorLine		INT,
					@ErrorMessage	NVARCHAR(4000)
				
   		SELECT		@ErrorNumber = ERROR_NUMBER(),
					@ErrorSeverity = ERROR_SEVERITY(),
					@ErrorState = ERROR_STATE(),
					@ErrorProcedure = ERROR_PROCEDURE(),
					@ErrorLine = ERROR_LINE(),
					@ErrorMessage = ERROR_MESSAGE()
				
   		RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
   	END CATCH

END


